We will write code to get you the data you need - then automate reporting tasks like summaries, plots, lists and make them downloadable for you clients
#establish database connection
con <-
dbConnect(
RMySQL::MySQL(),
host = "mysql.query.s44",
username = "elessert",
password = .elessert_pass,
port = 3306,
dbname = "s44_prod_eden"
)
#get the data from SQL
LoginData <-
dbGetQuery(
con,
"select c.name, u.company_id, u.job_title, u.first_name, u.last_name, u.total_logins, u.last_login
from user u
join company c
on u.company_id = c.id
where u.first_name not like '%s44%'
and u.first_name not like '%test%'
and u.last_name not like '%s44%'
and u.first_name not like '%task%'
and u.email_address not like '%@sourceintelligence%'
and u.email_address not like '%@s%44%'
and u.email_address like '%@%'
and u.company_id != 1
and u.total_logins not like '%null%'
order by total_logins asc;"
)
LoginData %>%
group_by(name) %>%
mutate(logins = sum(total_logins)) %>%
arrange(desc(logins)) %>%
select(name, logins) %>%
unique %>%
head(15) %>% pander
| name | logins |
|---|---|
| Coca-Cola | 6901 |
| The Home Depot, Inc. | 3355 |
| VF Corporation | 1434 |
| Schneider Electric | 1417 |
| Macy’s, Inc. | 1183 |
| Lab 126 | 1072 |
| Cabela’s Inc. | 962 |
| SCHLUMBERGER | 869 |
| Perry Ellis International | 853 |
| Multi-Fineline Electronix Inc | 813 |
| Nordstrom, Inc. | 762 |
| GUESS?, Inc. | 721 |
| Johnson & Johnson Family of Companies | 678 |
| MSC Industrial Direct Co., Inc. | 652 |
| ANN INC. | 608 |
cons <- dbListConnections(MySQL());lapply(cons, dbDisconnect);rm(cons)
## [[1]]
## [1] TRUE
here is the same example without code
## [[1]]
## [1] TRUE
Here is where we can really shine - customers can get plots that they could never make in Excel, for example:
Note that we can also create presentation ready graphs for the customer contacts that need them for their internal reports